package com.sys.service;

import com.sys.GlobalVariable;
import com.sys.entity.Course;
import com.sys.entity.Trainer;
import com.sys.exception.DuplicateCardException;
import com.sys.utils.DbUtil4;
import com.sys.utils.DbUtil4Factory;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CourseService {
    public List<Course> showList(String cname, String tid) throws Exception {
        DbUtil4 db = null;
        List<Course> list = null;
        String sql = "SELECT a.cid,a.cname,a.ctime,b.place AS place,c.tname AS tname,a.stat,a.produce FROM t_course AS a,t_place AS b,t_trainer AS c WHERE a.cplace = b.pid AND a.tid = c.tid and del=0";

        List paramList = new ArrayList();

        if(cname!=null && !"".equals(cname)){
            sql += " and cname like ?";
            paramList.add(cname+"%");
        }
        if(tid!=null && !"".equals(tid)){
            sql += " and a.tid = ?";
            paramList.add(tid);
        }

        sql += " order by cid ";
        try {

            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, paramList.toArray() , Course.class);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }

    public List<Map<String,Object>> showTrainerList() throws Exception {
        DbUtil4 db = null;
        List<Map<String,Object>> list = null;
        String sql = "SELECT tid,tname FROM t_trainer";
        sql += " order by tid ";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql,null);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }

    public void add(String cname,String ctime,String cplace,String tid,String stat,String produce) throws Exception {
        DbUtil4 db = null;
        String selectSQL = "select count(1) as num FROM t_course where cname = ? and ctime= ? and cplace= ? and tid = ? and stat = ?";
        String sql = "INSERT INTO t_course (cname,ctime,cplace,tid,stat,produce) VALUES (?,?,?,?,?,?)";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            List<Map<String, Object>> list = db.query(selectSQL, new Object[]{cname,ctime,cplace,tid,stat});
            Long snum = (Long)  list.get(0).get("num");
            if(snum == 0){ // 没有重复的记录
                db.update(sql, new Object[]{cname,ctime,cplace,tid,stat,produce});
            }else{ // 如果名片存在，抛出异常
                throw new DuplicateCardException("名片已经存在！");
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }

    public Course showById(String cid) throws Exception{
        List<Course> list = null;
        DbUtil4 db = null;
        boolean t = false;

        String sql = "SELECT cname,ctime,cplace,tid,stat,produce FROM t_course WHERE cid=? AND del = 0";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, new Object[]{cid}, Course.class);
            if(list!=null && !list.isEmpty()){
                Course course = list.get(0);
                return course;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
        return null;
    }
    public int update(String cname, String ctime,String cplace,String tid,String stat,String produce, int cid) throws Exception {
        DbUtil4 db = null;
        String sql = "update t_course set cname = ?, ctime = ?,cplace = ?, tid = ?,stat = ?,produce = ? WHERE cid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int i = db.update(sql, new Object[]{cname,ctime,cplace,tid,stat,produce,cid});
            return i;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }

    public int delete(int cid) throws Exception {
        DbUtil4 db = null;
        String sql = "UPDATE t_course SET del = 1 WHERE cid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int j = db.update(sql, new Object[]{cid});
            return j;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
}
